package com.example.aidocengine.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.IOException;

@Component
public class TerminologyExcelReader {
    
    public void readTerminologyExcel() {
        String filePath = "E:/java_projects/ai-doc-portal/test-results/附件1：程序化启动方案术语库（含三个子表）20250907（项目校核版）.xlsx";
        
        try (FileInputStream fis = new FileInputStream(filePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {
            
            System.out.println("=== 术语库Excel文件分析 ===");
            System.out.println("工作表数量: " + workbook.getNumberOfSheets());
            
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                System.out.println("\n工作表 " + (i+1) + ": " + sheetName);
                
                // 重点关注程序化术语模板相关的工作表
                if (sheetName.contains("程序化术语模板") || sheetName.contains("二次设备") || sheetName.contains("一次设备")) {
                    System.out.println("*** 发现术语模板工作表: " + sheetName + " ***");
                    analyzeSheet(sheet);
                }
            }
            
        } catch (IOException e) {
            System.err.println("读取Excel文件失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private void analyzeSheet(Sheet sheet) {
        if (sheet == null) return;
        
        int totalRows = sheet.getLastRowNum() + 1;
        System.out.println("总行数: " + totalRows);
        
        // 分析表头
        Row headerRow = sheet.getRow(0);
        if (headerRow != null) {
            System.out.println("表头信息:");
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    System.out.print("列" + (i+1) + ": [" + getCellValue(cell) + "] ");
                }
            }
            System.out.println();
        }
        
        // 输出前20行数据来了解结构
        int maxRows = Math.min(totalRows, 20);
        System.out.println("前" + maxRows + "行数据:");
        
        for (int rowIndex = 0; rowIndex < maxRows; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;
            
            System.out.print("第" + (rowIndex + 1) + "行: ");
            for (int cellIndex = 0; cellIndex < Math.min(row.getLastCellNum(), 8); cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                String value = getCellValue(cell);
                if (!value.trim().isEmpty()) {
                    System.out.print("[" + value + "] ");
                } else {
                    System.out.print("[空] ");
                }
            }
            System.out.println();
        }
        System.out.println("========================");
    }
    
    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return cell.toString().trim();
        }
    }
}